import logging
from datetime import date
from typing import Optional, List

from fastapi import APIRouter, Query, Depends, Path
from pydantic import Field, BaseModel
from sqlalchemy import text
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session

from commons.BaseResponse import BaseResponse
from commons.PaginationBase import PaginationBase
from datebase import get_db
from utils.EncryptionUtil import oauth2_scheme

repair_head_router = APIRouter(prefix="/fault/repairHead", tags=["案件登记管理"], dependencies=[Depends(oauth2_scheme)])


class RepairSearch(PaginationBase):
    fsiteNo: Optional[str] = Field(None, description="站点编号")
    ftakedateStart: Optional[str] = Field(None, description="开始日期", max_length=10)
    ftakedateEnd: Optional[str] = Field(None, description="结束日期", max_length=10)
    fpointNo: Optional[str] = Field(None, description="点位编号")
    fbillno: Optional[str] = Field(None, description="案件编号")
    freportuser: Optional[str] = Field(None, description="报案人")
    frepairuser: Optional[str] = Field(None, description="维修人")
    fbillstate: Optional[str] = Field(None, description="案件状态")


@repair_head_router.get("/list", summary="查询维修信息")
def list_repair_head(repair: RepairSearch = Query(), db: Session = Depends(get_db)):
    param = {}
    sql = "select count(1) from lims.s_tc_repair_head where 1=1"
    if repair.fsiteNo is not None:
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = repair.fsiteNo
    if repair.fpointNo is not None:
        sql = sql + " and fpoint_no = :fpointNo "
        param["fpointNo"] = repair.fpointNo
    if repair.fbillno is not None:
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = repair.fbillno
    if repair.frepairuser is not None:
        sql = sql + " and frepairuser = :frepairuser "
        param["frepairuser"] = repair.frepairuser
    if repair.freportuser is not None:
        sql = sql + " and freportuser = :freportuser "
        param["freportuser"] = repair.freportuser
    if repair.fbillstate is not None:
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = repair.fbillstate
    if (repair.ftakedateStart is not None) and (repair.ftakedateEnd is not None):
        sql = sql + " and to_char(freportdate,'yyyymmdd') between :ftakedateStart  and  :ftakedateEnd "
        param["ftakedateStart"] = repair.ftakedateStart
        param["ftakedateEnd"] = repair.ftakedateEnd
    count = db.execute(text(sql), param).scalar()

    param = {}
    sql = "select fbillno,fsite_no,freportuser,freportdate,fpoint_no,fequipmentbarcode,feuipmentid,"
    sql = sql + " freporttype,ferrormemo,ferrorlevel,frepairuser,frecdate,fcfdate,fdddate,fwcdate,"
    sql = sql + " flkdate,fbjnum,fbjnote,fupddate,foperator,fcheckdate,fcheckuser,fbillstate"
    sql = sql + " from lims.s_tc_repair_head where 1=1"
    if repair.fsiteNo is not None:
        sql = sql + " and fsite_no = :site_no "
        param["site_no"] = repair.fsiteNo
    if repair.fpointNo is not None:
        sql = sql + " and fpoint_no = :fpointNo "
        param["fpointNo"] = repair.fpointNo
    if repair.fbillno is not None:
        sql = sql + " and fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = repair.fbillno
    if repair.frepairuser is not None:
        sql = sql + " and frepairuser = :frepairuser "
        param["frepairuser"] = repair.frepairuser
    if repair.freportuser is not None:
        sql = sql + " and freportuser = :freportuser "
        param["freportuser"] = repair.freportuser
    if repair.fbillstate is not None:
        sql = sql + " and fbillstate = :fbillstate "
        param["fbillstate"] = repair.fbillstate
    if (repair.ftakedateStart is not None) and (repair.ftakedateEnd is not None):
        sql = sql + " and to_char(freportdate,'yyyymmdd') between :ftakedateStart  and  :ftakedateEnd "
        param["ftakedateStart"] = repair.ftakedateStart
        param["ftakedateEnd"] = repair.ftakedateEnd
    if repair.order == "desc":
        sql = sql + " order by  freportdate,fbillno desc "
    else:
        sql = sql + " order by  freportdate,fbillno  "
    sql = sql + " limit :limit  offset :offset "
    param["limit"] = repair.limit
    param["offset"] = repair.offset
    data = db.execute(text(sql), param).fetchall()

    # details 明细列表
    param = {}
    sql_3 = " select b.fbillno,b.fline,b.fimage,b.fmemo"
    sql_3 = sql_3 + " from lims.s_tc_repair_head a,lims.s_tc_repair_image b where a.fbillno=b.fbillno "
    if repair.fsiteNo is not None:
        sql_3 = sql_3 + " and fsite_no = :site_no "
        param["site_no"] = repair.fsiteNo
    if repair.fpointNo is not None:
        sql_3 = sql_3 + " and fpoint_no = :fpointNo "
        param["fpointNo"] = repair.fpointNo
    if repair.fbillno is not None:
        sql_3 = sql_3 + " and a.fbillno like concat('%', :fbillno ,'%')"
        param["fbillno"] = repair.fbillno
    if repair.frepairuser is not None:
        sql_3 = sql_3 + " and frepairuser = :frepairuser "
        param["frepairuser"] = repair.frepairuser
    if repair.freportuser is not None:
        sql_3 = sql_3 + " and freportuser = :freportuser "
        param["freportuser"] = repair.freportuser
    if repair.fbillstate is not None:
        sql_3 = sql_3 + " and fbillstate = :fbillstate "
        param["fbillstate"] = repair.fbillstate
    if (repair.ftakedateStart is not None) and (repair.ftakedateEnd is not None):
        sql_3 = sql_3 + " and to_char(freportdate,'yyyymmdd') between :ftakedateStart and :ftakedateEnd "
        param["ftakedateStart"] = repair.ftakedateStart
        param["ftakedateEnd"] = repair.ftakedateEnd
    sql_3 = sql_3 + " order by b.fbillno,b.fline "

    data_detail = db.execute(text(sql_3), param).fetchall()

    data_list = []
    for key in range(len(data)):
        d1 = {}
        d1['fbillno'] = data[key][0]
        d1['fsiteno'] = data[key][1]
        d1['freportuser'] = data[key][2]
        d1['freportdate'] = data[key][3]
        d1['fpointNo'] = data[key][4]
        d1['fequipmentbarcode'] = data[key][5]
        d1['feuipmentid'] = data[key][6]
        d1['freporttype'] = data[key][7]
        d1['ferrormemo'] = data[key][8]
        d1['ferrorlevel'] = data[key][9]
        d1['frepairuser'] = data[key][10]
        d1['frecdate'] = data[key][11]
        d1['fcfdate'] = data[key][12]
        d1['fdddate'] = data[key][13]
        d1['fwcdate'] = data[key][14]
        d1['flkdate'] = data[key][15]
        d1['fbjnum'] = data[key][16]
        d1['fbjnote'] = data[key][17]
        d1['fupddate'] = data[key][18]
        d1['foperator'] = data[key][19]
        d1['fcheckdate'] = data[key][20]
        d1['fcheckuser'] = data[key][21]
        d1['fbillstate'] = data[key][22]
        sbillno = data[key][0]

        detail_list = []
        for j1 in range(len(data_detail)):
            if (str(data_detail[j1][0]) == str(sbillno)):
                d2 = {}
                d2['fbillno'] = data_detail[j1][0]
                d2['fline'] = data_detail[j1][1]
                d2['fimage'] = data_detail[j1][2]
                d2['fmemo'] = data_detail[j1][3]
                detail_list.append(d2)

        d1["details"] = detail_list
        data_list.append(d1)

    info_dict = {"list": data_list,
                 "limit": repair.limit,
                 "page": repair.pageIndex,
                 "pages": (count + repair.limit - 1) // repair.limit,
                 "total": count}
    return BaseResponse.success(data=info_dict)


class RepairImageDetail(BaseModel):
    """案件照片明细实体类"""
    fline: int = Field(..., description="行号", ge=1)
    fimage: str = Field(..., description="图片路径", min_length=1)
    fmemo: Optional[str] = Field(None, description="备注", max_length=255)


class RepairErrorDetail(BaseModel):
    """故障列表明细实体类"""
    fline: int = Field(..., description="行号", ge=1)
    ferrorno: int = Field(..., description="故障编号", ge=1)
    fmemo: Optional[str] = Field(None, description="故障备注", max_length=500)


class RepairProcDetail(BaseModel):
    """解决步骤明细实体类"""
    fprocmemo: str = Field(..., description="处理步骤描述", min_length=1)


class RepairPartDetail(BaseModel):
    """设备零件明细实体类"""
    fline: int = Field(..., description="行号", ge=1)
    fpartno: str = Field(..., description="零件编号", min_length=1)
    fpartmac: str = Field("", description="零件MAC地址", max_length=50)
    fbuyprice: float = Field(0.0, description="购买价格", ge=0)
    fqty: int = Field(1, description="数量", ge=1)
    ftotamt: float = Field(0.0, description="总金额", ge=0)
    fmemo: Optional[str] = Field(None, description="零件备注", max_length=500)


class RepairHeadCreate(BaseModel):
    """案件登记主表创建实体类"""
    fbillno: str = Field(..., description="案件编号", min_length=1, max_length=50)
    fsiteNo: str = Field(..., description="站点编号", min_length=1, max_length=50)
    freportuser: str = Field(..., description="报案人", min_length=1, max_length=50)
    freportdate: date = Field(..., description="报案日期")
    fpointNo: str = Field(..., description="点位编号", min_length=1, max_length=50)
    fequipmentbarcode: str = Field(..., description="设备条码", min_length=1, max_length=100)
    feuipmentid: int = Field(..., description="设备ID", ge=1)
    freporttype: int = Field(..., description="报案类型", ge=1)
    ferrorlevel: int = Field(..., description="故障级别", ge=1, le=5)
    frepairuser: int = Field(..., description="维修人ID", ge=1)
    frecdate: date = Field(..., description="受理日期")
    fupddate: date = Field(..., description="更新日期")
    foperator: int = Field(..., description="操作人ID", ge=1)
    fbillstate: int = Field(..., description="案件状态", ge=0, le=3)
    ferrormemo: Optional[str] = Field(None, description="故障描述", max_length=1000)
    fequipmenttag: Optional[str] = Field(None, description="设备标签", max_length=100)

    details: Optional[List[RepairImageDetail]] = Field(None, description="案件照片明细")


class RepairHeadUpdate(BaseModel):
    """案件登记主表更新实体类"""
    fbillno: str = Field(..., description="案件编号", min_length=1, max_length=50)
    fbillstate: Optional[int] = Field(None, description="案件状态", ge=0, le=3)
    ferrorlevel: Optional[int] = Field(None, description="故障级别", ge=1, le=5)
    foperator: Optional[int] = Field(None, description="操作人ID", ge=1)
    frecdate: Optional[str] = Field(None, description="受理日期", max_length=30)
    frepairuser: Optional[int] = Field(None, description="维修人ID", ge=1)
    freportdate: Optional[str] = Field(None, description="报案日期", max_length=30)
    freporttype: Optional[int] = Field(None, description="报案类型", ge=1)
    freportuser: Optional[str] = Field(None, description="报案人", max_length=50)
    fupddate: Optional[str] = Field(None, description="更新日期", max_length=30)
    fcfdate: Optional[str] = Field(None, description="初检日期", max_length=30)
    fdddate: Optional[str] = Field(None, description="定损日期", max_length=30)
    fwcdate: Optional[str] = Field(None, description="完工日期", max_length=30)
    flkdate: Optional[str] = Field(None, description="领款日期", max_length=30)
    ferrormemo: Optional[str] = Field(None, description="故障描述", max_length=1000)
    fbjnote: Optional[str] = Field(None, description="备件备注", max_length=500)
    fbjnum: Optional[int] = Field(None, description="备件数量", ge=0)

    details: Optional[List[RepairImageDetail]] = Field(None, description="案件照片明细")
    details2: Optional[List[RepairErrorDetail]] = Field(None, description="故障列表")
    details3: Optional[List[RepairProcDetail]] = Field(None, description="解决步骤")
    details4: Optional[List[RepairPartDetail]] = Field(None, description="设备零件")


@repair_head_router.post("/create", summary="新增维修信息")
def create_repair_head(repair: RepairHeadCreate, db: Session = Depends(get_db)):
    try:
        fbillno = repair.fbillno
        # 返回内容
        d1 = {}
        detail_list = []
        with db.begin():
            # 新增详情
            jsondata = repair.details
            for data in jsondata:
                sql = "insert into lims.s_tc_repair_image(fbillno,fline,fimage,fmemo)"
                sql = sql + " values( :fbillno , :fline , :fimage , :fmemo )"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": data.fline,
                    "fimage": data.fimage,
                    "fmemo": data.fmemo
                })
                # 返回明细
                d2 = {}
                d2['fbillno'] = fbillno
                d2['fline'] = data.fline
                d2['fimage'] = data.fimage
                d2['fmemo'] = data.fmemo
                detail_list.append(d2)

            sql = (
                "insert into lims.s_tc_repair_head(fbillno,fsite_no,freportuser,freportdate,fpoint_no,fequipmentbarcode,"
                "feuipmentid,freporttype,ferrormemo,ferrorlevel,frepairuser,frecdate,fupddate,foperator,fbillstate,fbjnum)")
            sql = sql + (" values( :fbillno , :fsiteNo , :freportuser , :freportdate , :fpointNo , :fequipmentbarcode ,"
                         " :feuipmentid , :freporttype , :ferrormemo , :ferrorlevel , :frepairuser , :frecdate , :fupddate , :foperator , :fbillstate ,0)")
            db.execute(text(sql), {
                "fbillno": fbillno,
                "fsiteNo": repair.fsiteNo,
                "freportuser": repair.freportuser,
                "freportdate": repair.freportdate,
                "fpointNo": repair.fpointNo,
                "fequipmentbarcode": repair.fequipmentbarcode,
                "feuipmentid": repair.feuipmentid,
                "freporttype": repair.freporttype,
                "ferrormemo": repair.ferrormemo,
                "ferrorlevel": repair.ferrorlevel,
                "frepairuser": repair.frepairuser,
                "frecdate": repair.frecdate,
                "fupddate": repair.fupddate,
                "foperator": repair.foperator,
                "fbillstate": repair.fbillstate,
            })

            # 返回
            d1['fbillno'] = fbillno
            d1['fbillstate'] = repair.fbillstate
            d1['fbjnote'] = None
            d1['fbjnum'] = None
            d1['fcfdate'] = None
            d1['fcheckdate'] = None
            d1['fcheckuser'] = None
            d1['fdddate'] = None
            d1['fequipmentbarcode'] = repair.fequipmentbarcode
            d1['fequipmenttag'] = repair.fequipmenttag
            d1['ferrorlevel'] = repair.ferrorlevel
            d1['ferrormemo'] = repair.ferrormemo
            d1['feuipmentid'] = repair.feuipmentid
            d1['flkdate'] = None
            d1['foperator'] = repair.foperator
            d1['fpointNo'] = repair.fpointNo
            d1['frecdate'] = repair.frecdate
            d1['frepairuser'] = repair.frepairuser
            d1['freportdate'] = repair.freportdate
            d1['freporttype'] = repair.freporttype
            d1['freportuser'] = repair.freportuser
            d1['fsiteNo'] = repair.frepairuser
            d1['fupddate'] = repair.fupddate
            d1['fwcdate'] = None
            d1["details"] = detail_list
            d1["details2"] = None
            d1["details3"] = None
            d1["details4"] = None
            return BaseResponse.success(data=d1)
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@repair_head_router.post("/update", summary="更新维修信息")
def update_repair_head(repair: RepairHeadUpdate, db: Session = Depends(get_db)):
    try:
        fbillno = repair.fbillno
        with db.begin():
            sql = "delete from lims.s_tc_repair_image where fbillno= :fbillno "
            db.execute(text(sql), {
                "fbillno": fbillno
            })
            # 新增图片详情
            jsondata = repair.details
            for data in jsondata:
                sql = "insert into lims.s_tc_repair_image(fbillno,fline,fimage,fmemo)"
                sql = sql + " values( :fbillno , :fline , :fimage , :fmemo )"
                db.execute(text(sql), {
                    "fbillno": fbillno,
                    "fline": data.fline,
                    "fimage": data.fimage,
                    "fmemo": data.fmemo
                })
            # 新增故障列表
            sql = "delete from lims.s_tc_repair_error where fbillno= :fbillno "
            db.execute(text(sql), {
                "fbillno": fbillno,
            })
            jsondata2 = repair.details2
            if jsondata2 is not None:
                for data in jsondata2:
                    sql = "insert into lims.s_tc_repair_error(fbillno,fline,ferrorno,fmemo)"
                    sql = sql + " values( :fbillno , :fline , :ferrorno , :fmemo )"
                    db.execute(text(sql), {
                        "fbillno": fbillno,
                        "fline": data.fline,
                        "ferrorno": data.ferrorno,
                        "fmemo": data.fmemo
                    })
            # 解决步骤
            sql = "delete from lims.s_tc_repair_proc where fbillno= :fbillno "
            db.execute(text(sql), {
                "fbillno": fbillno,
            })

            iline = 1
            jsondata3 = repair.details3
            if jsondata3 is not None:
                for data in jsondata3:
                    sql = "insert into lims.s_tc_repair_proc(fbillno,fline,fprocmemo)"
                    sql = sql + " values( :fbillno , :iline , :fprocmemo )"
                    db.execute(text(sql), {
                        "fbillno": fbillno,
                        "fline": iline,
                        "fprocmemo": data.fprocmemo
                    })
                    iline = iline + 1
            # 设备零件
            sql = "delete from lims.s_tc_repair_part where fbillno= :fbillno "
            db.execute(text(sql), {
                "fbillno": fbillno,
            })

            jsondata4 = repair.details4
            if jsondata4 is not None:
                for data in jsondata4:
                    sql = "insert into lims.s_tc_repair_part(fbillno,fline,fpartno,fpartmac,fbuyprice,fqty,ftotamt,fmemo)"
                    sql = sql + " values( :fbillno ,:fline , :fpartno , :fpartmac ,0, :fqty ,0, :fmemo )"
                    db.execute(text(sql), {
                        "fbillno": fbillno,
                        "fline": data.fline,
                        "fpartno": data.fpartno,
                        "fpartmac": data.fpartmac,
                        "fqty": data.fqty,
                        "fmemo": data.fmemo
                    })

            param = {}
            sql = "update lims.s_tc_repair_head "
            sql = sql + "set ferrormemo= :ferrormemo ,"
            param["ferrormemo"] = repair.ferrormemo
            sql = sql + "fbjnote = :fbjnote ,"
            param["fbjnote"] = repair.fbjnote
            sql = sql + "fbjnum =  :fbjnum ,"
            param["fbjnum"] = repair.fbjnum
            if repair.fdddate is not None:
                sql = sql + "fdddate = TO_TIMESTAMP( :fdddate ,'YYYY-MM-DD HH24:MI:SS'),"
                param["fdddate"] = repair.fdddate
            if repair.flkdate is not None:
                sql = sql + "flkdate = TO_TIMESTAMP( :flkdate ,'YYYY-MM-DD HH24:MI:SS'),"
                param["flkdate"] = repair.flkdate
            sql = sql + "frepairuser =  :freportuser ,"
            param["freportuser"] = repair.freportuser
            if repair.freportdate is not None:
                sql = sql + "freportdate =TO_TIMESTAMP( :freportdate ,'Dy, DD Mon YYYY HH24:MI:SS GMT'),"
                param["freportdate"] = repair.freportdate
            sql = sql + "freporttype = :freporttype ,"
            param["freporttype"] = repair.freporttype
            sql = sql + "freportuser = :freportuser ,"
            param["freportuser"] = repair.freportuser
            if repair.fwcdate is not None:
                sql = sql + "fwcdate =TO_TIMESTAMP( :fwcdate ,'YYYY-MM-DD HH24:MI:SS'),"
                param["fwcdate"] = repair.fwcdate
            if repair.fcfdate is not None:
                sql = sql + "fcfdate =TO_TIMESTAMP( :fcfdate ,'YYYY-MM-DD HH24:MI:SS'),"
                param["fcfdate"] = repair.fcfdate
            sql = sql + "fbillstate =  :fbillstate ,"
            param["fbillstate"] = repair.fbillstate
            if repair.frecdate is not None:
                sql = sql + "frecdate =TO_TIMESTAMP( :frecdate ,'Dy, DD Mon YYYY HH24:MI:SS GMT'),"
                param["frecdate"] = repair.frecdate
            sql = sql + "ferrorlevel =  :ferrorlevel ,"
            param["ferrorlevel"] = repair.ferrorlevel
            if repair.fupddate is not None:
                sql = sql + "fupddate  = TO_TIMESTAMP( :fupddate ,'YYYY-MM-DD HH24:MI:SS')" + ","
                param["fupddate"] = repair.fupddate
            sql = sql + "foperator = :foperator "
            param["foperator"] = repair.foperator
            db.execute(text(sql), param)
            return BaseResponse.success()
    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")


@repair_head_router.post("/delete/{fbillno}", summary="删除维修记录")
def delete_repair_head(fbillno: str = Path(description="单号"), db: Session = Depends(get_db)):
    try:
        with db.begin():
            sql = "select count(1) from lims.s_tc_repair_head where fbillno= :fbillno  and fbillstate>1"

            data = db.execute(text(sql), {
                "fbillno": fbillno,
            }).scalar()
            if data > 0:
                return BaseResponse.error(message="已完成订单不能被删除")
            else:

                sql = "delete from lims.s_tc_repair_error where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fbillno": fbillno,
                })
                sql = "delete from lims.s_tc_repair_image where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fbillno": fbillno,
                })
                sql = "delete from lims.s_tc_repair_part where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fbillno": fbillno,
                })
                sql = "delete from lims.s_tc_repair_proc where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fbillno": fbillno,
                })
                sql = "delete from lims.s_tc_repair_head where fbillno= :fbillno "
                db.execute(text(sql), {
                    "fbillno": fbillno,
                })
                return BaseResponse.success()

    except SQLAlchemyError as e:
        # 发生异常时自动回滚
        db.rollback()
        # 记录错误日志
        logging.error(f"数据库错误: {str(e)}")
        # 返回错误响应
        return BaseResponse.error(message="数据库操作失败")
    except Exception as e:
        db.rollback()
        logging.error(f"未知错误: {str(e)}")
        return BaseResponse.error(message="令牌过期")
